# -*- coding: utf-8 -*-#
__author__ = 'yangq'
import os
from tornado import gen
from common import timetool
from appcommon.appinstance import mysql_app_pool
import settings


@gen.coroutine
def add_err_cnt(sub_id, error):
    sql = (
        "select sub_id, err_cnt, total_cnt from tiku_err_cnt where sub_id = {} "
    )
    exists = yield mysql_app_pool.get_one(sql.format(sub_id), cursor='dict')
    if exists:
        err_cnt = exists['err_cnt'] + 1 if error == '1' else exists['err_cnt']
        total_cnt = exists['total_cnt'] + 1
        sql_u = (
            "update tiku_err_cnt set err_cnt = {}, total_cnt = {}  where sub_id = {} "
        )
        yield mysql_app_pool.update(sql_u.format(err_cnt, total_cnt, sub_id))

    else:
        err_cnt = 1 if error == '1' else 0
        total_cnt = 1
        sql_i = (
            "insert into tiku_err_cnt (sub_id, err_cnt, total_cnt) values ({}, {}, {})"
        )
        yield mysql_app_pool.insert_one(sql_i.format(sub_id, err_cnt, total_cnt))
    res = dict(
        sub_id=sub_id,
        err_cnt=err_cnt,
        total_cnt=total_cnt
    )
    raise gen.Return(res)


@gen.coroutine
def get_err_cnt():
    sql = (
        "select a.sub_id, IFNULL(b.err_cnt,0) as err_cnt, IFNULL(b.total_cnt,0) as total_cnt from tiku a LEFT JOIN tiku_err_cnt b "
        "on a.sub_id = b.sub_id where a.is_using = 1 order by err_cnt desc"
    )
    err_cnt_list = yield mysql_app_pool.get_all(sql, cursor='dict')
    list_len = len(err_cnt_list)
    for index, item in enumerate(err_cnt_list):
        if index >= 0 and index < list_len * 1 / 15:
            item['stars'] = 5
        elif index >= list_len * 1 / 15 and index < list_len * 3 / 15:
            item['stars'] = 4
        elif index >= list_len * 3 / 15 and index < list_len * 6 / 15:
            item['stars'] = 3
        elif index >= list_len * 6 / 15 and index < list_len * 10 / 15:
            item['stars'] = 2
        elif index >= list_len * 10 / 15 and index < list_len * 15 / 15:
            item['stars'] = 1
        else:
            item['stars'] = 0
    # info = dict(
    #     sub_id=sub_id,
    #     err_cnt=0,
    #     stars=0
    # )
    # # for index, item in enumerate(err_cnt_list):
    #     if item['sub_id'] == sub_id:
    #         info['err_cnt'] = item['err_cnt']
    #         if index < 100:
    #             info['stars'] = 5
    #         elif index >= 100 and index < 200:
    #             info['stars'] = 4
    #         elif index >= 200 and index < 300:
    #             info['stars'] = 3
    #         elif index >= 300 and index < 400:
    #             info['stars'] = 2
    #         elif index >= 400 and index < 500:
    #             info['stars'] = 1
    #         else:
    #             info['stars'] = 0
    # a0 = 0
    # a1 = 1
    # a2 = 2
    # a3 = 3
    # a4 = 4
    # a5 = 5
    # for item in err_cnt_list:
    #     if item['stars'] == 0:
    #         a0 += 1
    #     elif item['stars'] == 1:
    #         a1 += 1
    #     elif item['stars'] == 2:
    #         a2 += 1
    #     elif item['stars'] == 3:
    #         a3 += 1
    #     elif item['stars'] == 4:
    #         a4 += 1
    #     elif item['stars'] == 5:
    #         a5 += 1
    # print a0
    # print a1
    # print a2
    # print a3
    # print a4
    # print a5

    raise gen.Return(err_cnt_list)


@gen.coroutine
def save_exam_result(current_time, phone, minutes, result, city, province, exam_type, car_type):
    sql = (
        "insert into tiku_exam_result (pubtime, phone, minutes, result, city, province, exam_type, car_type) values "
        u"({}, '{}', '{}', {}, '{}', '{}', {}, '{}')"
    )
    yield mysql_app_pool.insert_one(sql.format(current_time, phone, minutes, result, city, province, exam_type, car_type))
    raise gen.Return(0)


@gen.coroutine
def get_exam_rank(type, scope, exam_type, car_type):
    if type == '1':
        param = u"and a.province = '{}'".format(scope)
    elif type == '2':
        param = u"and a.city = '{}'".format(scope)
    else:
        param = ""
    if car_type:
        param += " and a.car_type = '{}' ".format(car_type)
    sql = (
        "select a.phone, a.minutes, a.result, a.pubtime, b.nick_name, b.avatar "
        "from tiku_exam_result a , app_user_info b "
        "where  a.phone=b.phone and a.exam_type={} and a.result <= 100 "
        u"{} "
        "ORDER BY a.result desc, a.minutes "
        "limit 100"
    )
    info = yield mysql_app_pool.get_all(sql.format(exam_type, param), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def get_video_list(car_type, type, pubtime, page_count):
    param = "and pubtime < {} ".format(pubtime) if pubtime else ""
    sql = "select * from tiku_video where type={} and car_type={} {} order by pubtime desc limit {}"
    info = yield mysql_app_pool.get_all(sql.format(type, car_type, param, page_count), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def get_video_detail(video_id):
    sql = "select * from tiku_video where id = {}"
    info = yield mysql_app_pool.get_one(sql.format(video_id), cursor='dict')
    raise gen.Return(info)


@gen.coroutine
def update_video_view_cnt(video_id):
    sql = "update tiku_video set view_cnt = view_cnt + 1 where id = {}"
    info = yield mysql_app_pool.update(sql.format(video_id))
    raise gen.Return(info)


@gen.coroutine
def get_new_tiku(pubtime):
    if pubtime:
        sql = "select * from tiku where pubtime > {}"
        info = yield mysql_app_pool.get_all(sql.format(pubtime), cursor='dict')
    else:
        info = []
    raise gen.Return(info)